package com.eleadmin.ocean.biz.impl;

import com.eleadmin.common.system.entity.User;
import com.eleadmin.damon.utils.constant.UserConstant;
import com.eleadmin.damon.utils.results.BaseDao;
import com.eleadmin.damon.utils.results.PageResult2;
import com.eleadmin.damon.utils.utils.Utils;
import com.eleadmin.ocean.biz.OceanBulkCargoFeeBiz;
import com.eleadmin.ocean.entity.OceanBulkCargo;
import com.eleadmin.ocean.entity.OceanBulkCargoFee;
import com.eleadmin.common.core.web.JsonResult;
import com.eleadmin.ocean.param.OceanBulkCargoParam;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.Map;
import java.util.List;

/**
 * 数据层实现类
 *
 * @author : Doman
 * @version ：1.0
 * @date : 2023-11-06 15:38:47
 */
@Repository
public class OceanBulkCargoFeeBizImpl extends BaseDao implements OceanBulkCargoFeeBiz {

    @Override
    public JsonResult list(PageResult2<T> page, OceanBulkCargoFee oceanBulkCargoFee, User loginUser) throws Exception {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<OceanBulkCargoFee> rm = new BeanPropertyRowMapper<OceanBulkCargoFee>(OceanBulkCargoFee.class);
        StringBuffer sql = new StringBuffer("select o.id,o.organization_id,o.bulk_cargo_id");
        sql.append(",o.type,o.fee_name,o.price,o.fee_modules");
        sql.append(",o.fee_modules_order,o.invoicename,o.invoicestate,o.currency");
        sql.append(",o.exchange_rate,o.remark,o.create_time,o.create_user");
        sql.append(",o.update_time,o.update_user");
        sql.append(",sogzn.organization_name organizationName,suser.nickname createName,suser2.nickname updateName ");
        sql.append(" from chf_ocean_bulk_cargo_fee o ");
        sql.append(" inner join sys_user suser on suser.user_id=o.create_user ");
        sql.append(" left join sys_user suser2 on suser2.user_id=o.update_user ");
        sql.append(" inner join sys_organization sogzn on sogzn.organization_id=o.organization_id and sogzn.deleted=0 ");
        sql.append(" where o.deleted=0 and o.bulk_cargo_id=:bulk_cargo_id ");
        map.put("bulk_cargo_id", oceanBulkCargoFee.getBulkCargoId());
        if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
            sql.append(" and o.organization_id=:tableOrganizationId ");
            map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
        }
        if (Utils.isNotEmpty(oceanBulkCargoFee)) {
            if (Utils.isNotEmpty(oceanBulkCargoFee.getType())) {
                sql.append(" and instr(o.type,:type)>0 ");
                map.put("type", oceanBulkCargoFee.getType());
            }
        }
        sql.append(" order by o.type asc,o.fee_modules_order, o.create_time desc");
        return jsonResult(getPageQueryList(page, sql.toString(), map, rm));
    }

    @Override
    public List<OceanBulkCargoFee> list(OceanBulkCargoFee oceanBulkCargoFee, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<OceanBulkCargoFee> rm = new BeanPropertyRowMapper<OceanBulkCargoFee>(OceanBulkCargoFee.class);
        StringBuffer sql = new StringBuffer("select o.id,o.organization_id,o.bulk_cargo_id");
        sql.append(",o.type,o.fee_name,o.price,o.fee_modules");
        sql.append(",o.fee_modules_order,o.invoicename,o.invoicestate,o.currency");
        sql.append(",o.exchange_rate,o.remark,o.create_time,o.create_user");
        sql.append(",o.update_time,o.update_user");
        sql.append(",sogzn.organization_name organizationName,suser.nickname createName ");
        sql.append(" from chf_ocean_bulk_cargo_fee o ");
        sql.append(" inner join sys_user suser on suser.user_id=o.create_user ");
        sql.append(" inner join sys_organization sogzn on sogzn.organization_id=o.organization_id and sogzn.deleted=0 ");
        sql.append(" where o.deleted=0 and o.bulk_cargo_id=:bulk_cargo_id ");
        map.put("bulk_cargo_id", oceanBulkCargoFee.getBulkCargoId());
        if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
            sql.append(" and o.organization_id=:tableOrganizationId ");
            map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
        }
        if (Utils.isNotEmpty(oceanBulkCargoFee)) {
            if (Utils.isNotEmpty(oceanBulkCargoFee.getType())) {
                sql.append(" and instr(o.type,:type)>0 ");
                map.put("type", oceanBulkCargoFee.getType());
            }
        }

        sql.append(" order by o.type asc,o.fee_modules_order, o.create_time desc");
        getMapParameter(sql, map);
        return jdbcTemplate.query(sql.toString(), map, rm);
    }

//    @Override
//    public List<OceanBulkCargoFee> listForExport(OceanBulkCargoParam oceanBulkCargoParam, User loginUser) {
//        Map<String, String> map = new HashMap<String, String>();
//        RowMapper<OceanBulkCargoFee> rm = new BeanPropertyRowMapper<OceanBulkCargoFee>(OceanBulkCargoFee.class);
//        StringBuffer sql = new StringBuffer("select o.id");
//        sql.append(",o.type,o.price");
//        sql.append(",o.fee_name");
//        sql.append(",o.fee_modules,o.invoicename,o.invoicestate");
//        sql.append(",o.currency,o.exchange_rate");
//        sql.append(" from chf_ocean_bulk_cargo_fee o ");
//        sql.append(" where o.deleted=0 and o.bulk_cargo_id=:bulk_cargo_id ");
//        map.put("bulk_cargo_id", oceanBulkCargoParam.getBulkCargoId());
//        if (Utils.isNotEmpty(oceanBulkCargoParam)) {
//            if (Utils.isNotEmpty(oceanBulkCargoParam.getType())) {
//                sql.append(" and o.type=:type ");
//                map.put("type", oceanBulkCargoParam.getType());
//            }
//            //客户名称缩写
//            if (Utils.isNotEmpty(oceanBulkCargoParam.getCustomerAbbreviation())) {
//                sql.append(" and o.invoicename=:invoicename ");
//                map.put("invoicename", oceanBulkCargoParam.getCustomerAbbreviation());
//            }
//        }
//        sql.append(" order by o.type asc,o.fee_modules_order, o.create_time desc");
//        getMapParameter(sql, map);
//        return jdbcTemplate.query(sql.toString(), map, rm);
//    }

    @Override
    public List<OceanBulkCargoFee> listForCustomerExport(OceanBulkCargoParam oceanBulkCargoParam, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<OceanBulkCargoFee> rm = new BeanPropertyRowMapper<OceanBulkCargoFee>(OceanBulkCargoFee.class);
        StringBuffer sql = new StringBuffer("");
        sql.append("select o.type,o.price");
//		sql.append(",CASE WHEN REGEXP_INSTR(c.fee_name, '^提')  THEN '提箱'  WHEN REGEXP_INSTR(c.fee_name, '^进')  THEN '进港' ELSE c.fee_name END as fee_name");
//        sql.append(",CASE WHEN fee_modules = '提箱'  THEN '提箱'  WHEN fee_modules = '进港'  THEN '进港' ELSE fee_name END fee_name ");
        sql.append(",o.fee_name");
        sql.append(",o.fee_modules,o.invoicename,o.invoicestate");
        sql.append(",o.currency,o.exchange_rate");
        sql.append(" from chf_ocean_bulk_cargo_fee o ");
        sql.append(" where o.deleted=0 and o.bulk_cargo_id=:bulk_cargo_id ");
        map.put("bulk_cargo_id", oceanBulkCargoParam.getId());
        if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
            sql.append(" and o.organization_id=:tableOrganizationId ");
            map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
        }
        if (Utils.isNotEmpty(oceanBulkCargoParam.getType())) {
            sql.append(" and o.type=:type ");
            map.put("type", oceanBulkCargoParam.getType());
        }
        //客户名称缩写
        if (Utils.isNotEmpty(oceanBulkCargoParam.getCustomerAbbreviation())) {
            sql.append(" and o.invoicename=:invoicename ");
            map.put("invoicename", oceanBulkCargoParam.getCustomerAbbreviation());
        }

        sql.append(" order by o.type asc,o.fee_modules_order, o.create_time desc");
        getMapParameter(sql, map);
        return jdbcTemplate.query(sql.toString(), map, rm);
    }

    @Override
    public List<String> feeNamesForCustomer(OceanBulkCargo oceanBulkCargo, User loginUser) {
        Map<String, Object> map = new HashMap<String, Object>();
        RowMapper<String> rm = new SingleColumnRowMapper<String>(String.class);
        StringBuffer sql = new StringBuffer("select ");
        sql.append(" distinct o.fee_name");
        sql.append(" from chf_ocean_bulk_cargo_fee o ");
        sql.append(" inner join chf_ocean_bulk_cargo obc on obc.id=o.bulk_cargo_id and obc.deleted=0 ");
        sql.append(" where o.deleted=0 ");
        if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
            sql.append(" and o.organization_id=:tableOrganizationId ");
            map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
        }
        if (Utils.isNotEmpty(oceanBulkCargo.getType())) {
            sql.append(" and o.type=:type ");
            map.put("type", oceanBulkCargo.getType());
        }
        if (Utils.isNotEmpty(oceanBulkCargo.getBillCode())) {
            sql.append(" and obc.bill_code=:bill_code ");
            map.put("bill_code", oceanBulkCargo.getBillCode());
        }
        if (Utils.isNotEmpty(oceanBulkCargo.getDate1())) {
            sql.append(" and obc.create_time>= :date1 ");
            map.put("date1", oceanBulkCargo.getDate1());
        }
        if (Utils.isNotEmpty(oceanBulkCargo.getDate2())) {
            sql.append(" and obc.create_time<=:date2 ");
            map.put("date2", oceanBulkCargo.getDate2());
        }
        if (Utils.isNotEmpty(oceanBulkCargo.getBillCode())) {
            String[] a = oceanBulkCargo.getBillCode().split(",");
//                StringBuffer c = new StringBuffer("'");
//                for (String aa : a) {
//                    c.append(aa+"','");
//                }
//                String cStr = c.toString().substring(0, c.length()-2);
            sql.append(" and obc.bill_code in (:bill_code) ");
            map.put("bill_code", a);
        }
        //甩手掌柜客户
        if (Utils.isNotEmpty(oceanBulkCargo.getCustomerAbbreviation())) {
            sql.append(" and o.invoicename=:invoicename ");
            map.put("invoicename", oceanBulkCargo.getCustomerAbbreviation());
        }
        getMapParameter(sql, null);
        return jdbcTemplate.query(sql.toString(), map, rm);
    }

    @Override
    public OceanBulkCargoFee getById(String id) {
        RowMapper<OceanBulkCargoFee> rm = new BeanPropertyRowMapper<OceanBulkCargoFee>(OceanBulkCargoFee.class);
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("select o.id,o.organization_id,o.bulk_cargo_id");
        sql.append(",o.type,o.fee_name,o.price,o.fee_modules");
        sql.append(",o.fee_modules_order,o.invoicename,o.invoicestate,o.currency");
        sql.append(",o.exchange_rate,o.remark,o.create_time,o.create_user");
        sql.append(",o.update_time,o.update_user");
        sql.append(",sogzn.organization_name organizationName,suser.nickname createName ");
        sql.append(" from chf_ocean_bulk_cargo_fee o ");
        sql.append(" inner join sys_user suser on suser.user_id=o.create_user ");
        sql.append(" inner join sys_organization sogzn on sogzn.organization_id=o.organization_id and sogzn.deleted=0 ");
        sql.append(" where o.deleted=0 and o.id=:id ");
        map.put("id", id);
        getMapParameter(sql, map);
        return jdbcTemplate.queryForObject(sql.toString(), map, rm);
    }

    @Override
    public boolean save(OceanBulkCargoFee oceanBulkCargoFee, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("insert into chf_ocean_bulk_cargo_fee (");
        sql.append("organization_id,bulk_cargo_id,type,fee_name");
        sql.append(",price,fee_modules,fee_modules_order,invoicename");
        sql.append(",invoicestate,currency,exchange_rate,remark");
        sql.append(",create_user,create_time) values ( ");
        sql.append(":organization_id,:bulk_cargo_id,:type,:fee_name");
        sql.append(",:price,:fee_modules,:fee_modules_order,:invoicename");
        sql.append(",:invoicestate,:currency,:exchange_rate,:remark");
        sql.append(",:create_user,now())");
        map.put("organization_id", loginUser.getOrganizationId() + "");
        map.put("bulk_cargo_id", oceanBulkCargoFee.getBulkCargoId());
        map.put("type", oceanBulkCargoFee.getType());
        map.put("fee_name", oceanBulkCargoFee.getFeeName());
        map.put("price", oceanBulkCargoFee.getPrice());
        map.put("fee_modules", oceanBulkCargoFee.getFeeModules());
        map.put("fee_modules_order", getOrder(oceanBulkCargoFee.getFeeModules()));
        map.put("invoicename", oceanBulkCargoFee.getInvoicename());
        map.put("invoicestate", oceanBulkCargoFee.getInvoicestate());
        map.put("currency", oceanBulkCargoFee.getCurrency());
        map.put("exchange_rate", oceanBulkCargoFee.getExchangeRate());
        map.put("remark", oceanBulkCargoFee.getRemark());
        map.put("create_user", loginUser.getUserId() + "");
        getMapParameter(sql, map);
        int count = jdbcTemplate.update(sql.toString(), map);
        return count > 0;
    }

    @Override
    public boolean update(OceanBulkCargoFee oceanBulkCargoFee, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("update chf_ocean_bulk_cargo_fee set ");
        sql.append("bulk_cargo_id=:bulk_cargo_id,type=:type,fee_name=:fee_name ");
        sql.append(",price=:price,fee_modules=:fee_modules,fee_modules_order=:fee_modules_order ");
        sql.append(",invoicename=:invoicename,invoicestate=:invoicestate,currency=:currency ");
        sql.append(",exchange_rate=:exchange_rate,remark=:remark,update_time=now() ");
        sql.append(",update_user=:update_user where id=:id ");
        map.put("bulk_cargo_id", oceanBulkCargoFee.getBulkCargoId());
        map.put("type", oceanBulkCargoFee.getType());
        map.put("fee_name", oceanBulkCargoFee.getFeeName());
        map.put("price", oceanBulkCargoFee.getPrice());
        map.put("fee_modules", oceanBulkCargoFee.getFeeModules());
        map.put("fee_modules_order", getOrder(oceanBulkCargoFee.getFeeModules()));
        map.put("invoicename", oceanBulkCargoFee.getInvoicename());
        map.put("invoicestate", oceanBulkCargoFee.getInvoicestate());
        map.put("currency", oceanBulkCargoFee.getCurrency());
        map.put("exchange_rate", oceanBulkCargoFee.getExchangeRate());
        map.put("remark", oceanBulkCargoFee.getRemark());
        map.put("update_user", loginUser.getUserId() + "");
        map.put("id", oceanBulkCargoFee.getId());
        getMapParameter(sql, map);
        int count = jdbcTemplate.update(sql.toString(), map);
        return count > 0;
    }

    public String getOrder(String feeModulesOrder) {
        if (feeModulesOrder.equals("运费")) {
            return "100";
        }
        if (feeModulesOrder.equals("国内杂费")) {
            return "200";
        }
        if (feeModulesOrder.equals("国外杂费")) {
            return "300";
        }
        if (feeModulesOrder.equals("后端派送费")) {
            return "400";
        }
        if (feeModulesOrder.equals("关税")) {
            return "500";
        }
        return "1";
    }

    @Override
    public boolean remove(String id, User loginUser) {
        int count = 0;
        Map<String, String> map = null;
        if (id.length() > 0) {
            for (String id1 : id.split(",")) {
                map = new HashMap<String, String>();
                if (id1 == null || id1.equals("")) {
                    break;
                }
                StringBuffer sql = new StringBuffer("update chf_ocean_bulk_cargo_fee set deleted=1" +
                        ",update_user=:update_user, update_time=now() "
                        + " where id =:id ");
                map.put("update_user", loginUser.getUserId() + "");
                map.put("id", id1);
                getMapParameter(sql, map);
                count = jdbcTemplate.update(sql.toString(), map);
            }
        }
        return count > 0;
    }
}
